package com.carple.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.carple.dto.Member;
import com.carple.dto.UploadFile;
import com.carple.dao.ConnectionHelper;


public class MemberDao {
	
	public void registerMember (Member member) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			
			conn = ConnectionHelper.getConnection();
			
			String sql = "INSERT INTO member "
				+ "(emailid, passwd, gender, isowner, carmodel, nickname, realname) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?);";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, member.getEmailId());
			pstmt.setString(2, member.getPasswd());
			pstmt.setString(3, member.getGender());
			pstmt.setString(4, member.getIsOwner());
			pstmt.setString(5, member.getCarModel());
			pstmt.setString(6, member.getNickName());
			pstmt.setString(7, member.getRealName());
			
			pstmt.executeUpdate();
			
	
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try {pstmt.close();} catch (Exception ex) {ex.printStackTrace();}
			try {conn.close();} catch (Exception ex) {ex.printStackTrace();}
		}		
	}
	
public void updateMember (Member member) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			
			conn = ConnectionHelper.getConnection();
			
			String sql = "UPDATE member "
				+ "SET fee = ?, ismarried = ? "
				+ "WHERE emailid = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(member.getFee()));
			pstmt.setString(2, member.getIsMarried());
			pstmt.setString(3, member.getEmailId());
			
			
			
			pstmt.executeUpdate();
			
	
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try {pstmt.close();} catch (Exception ex) {ex.printStackTrace();}
			try {conn.close();} catch (Exception ex) {ex.printStackTrace();}
		}		
	}

	public Member getMemberByIdAndPasswd(String id, String passwd) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;
		try{
	
			conn = ConnectionHelper.getConnection();
			String sql = "SELECT emailid, passwd, gender, isowner,"
					+ "carmodel, nickname, realname, fee, ismarried "
					+ "FROM member "
					+ "WHERE emailid=? AND passwd = ?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, passwd);
			rs = pstmt.executeQuery();	
			if(rs.next()){
				member = new Member();
				member.setEmailId(rs.getString(1));
				member.setPasswd(rs.getString(2));
				member.setGender(rs.getString(3));
				member.setIsOwner(rs.getString(4));
				member.setCarModel(rs.getString(5));
				member.setNickName(rs.getString(6));
				member.setRealName(rs.getString(7));
				member.setFee(rs.getString(8));
				member.setIsMarried(rs.getString(9));
			}
		}catch(Exception ex){ex.printStackTrace();}
		finally{
			try{rs.close();}catch(Exception ex){ex.printStackTrace();}
			try{pstmt.close();}catch(Exception ex){ex.printStackTrace();}
			try{conn.close();}catch(Exception ex){ex.printStackTrace();}
		}
		return member;	
	}

	public void modifyMember(Member member) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			
			conn = ConnectionHelper.getConnection();
			
			String sql = "UPDATE member "
				+ "SET passwd = ?, gender = ?, isowner = ?, carmodel = ?, nickname = ?, realname = ? "
				+ "WHERE emailid = ?";
			
						
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, member.getPasswd());
			pstmt.setString(2, member.getGender());
			pstmt.setString(3, member.getIsOwner());
			pstmt.setString(4, member.getCarModel());
			pstmt.setString(5, member.getNickName());
			pstmt.setString(6, member.getRealName());
			pstmt.setString(7, member.getEmailId());
			
			pstmt.executeUpdate();
			
	
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally {
			try {pstmt.close();} catch (Exception ex) {ex.printStackTrace();}
			try {conn.close();} catch (Exception ex) {ex.printStackTrace();}
		}		
		
	}
	
	
	//////////////////////////////////////////////////////////////
	////프로필사진
	//////////////////////////////////////////////////////////////
	
	
	public void insertProfileFile(UploadFile file) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"INSERT INTO uploadfile " + 
				"(emailid, savedfilename, userfilename) " +
				"VALUES (?, ?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, file.getEmailId());
			pstmt.setString(2, file.getSavedFileName());
			pstmt.setString(3, file.getUserFileName());
			pstmt.executeUpdate();
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { pstmt.close(); } catch (Exception ex) {ex.printStackTrace();}
			try { conn.close(); } catch (Exception ex) {ex.printStackTrace(); }
		}		
		
	}
	
	public UploadFile getUploadFileByUploadFileNo(String emailId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		UploadFile file = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"SELECT uploadfileno, emailid, savedfilename, userfilename " + 
				"FROM uploadfile WHERE emailId = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emailId);
			rs = pstmt.executeQuery();
				
			if (rs.next()) {
				file = new UploadFile();
				file.setUploadFileNo(rs.getInt(1));
				file.setEmailId(rs.getString(2));
				file.setSavedFileName(rs.getString(3));
				file.setUserFileName(rs.getString(4));
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		return file;	
	}
	
	public String getFileName(String emailId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String filename = null;
		
		try {
			conn = ConnectionHelper.getConnection();
			String sql = 
				"SELECT savedfilename " + 
				"FROM uploadfile WHERE emailId = ? "+
				"ORDER BY uploadfileno desc "+
				"LIMIT 0,1 ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emailId);
			rs = pstmt.executeQuery();
				
			if (rs.next()) {
				filename = rs.getString(1);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		return filename;
	}
}
